/**
 * 财务控制器
 * 处理财务相关的数据库操作
 */

import { BaseController, ensureDbInit, createResponse, createErrorResponse } from './BaseController'
import database from '../../utils/database'
import { generateId } from '../../mock/utils'

export class FinanceController extends BaseController {
  constructor() {
    super('finance_accounts')
  }
  
  /**
   * 获取账户列表
   */
  async getAccounts(params: {
    page?: number
    pageSize?: number
    type?: string
    isActive?: boolean
  } = {}) {
    try {
      const { page = 1, pageSize = 20, type, isActive } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams: any[] = []
      
      if (type) {
        whereClause += ' AND type = ?'
        whereParams.push(type)
      }
      
      if (isActive !== undefined) {
        whereClause += ' AND is_active = ?'
        whereParams.push(isActive ? 1 : 0)
      }
      
      return await this.getList({
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'created_at DESC'
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取账户列表失败')
    }
  }
  
  /**
   * 创建账户
   */
  async createAccount(data: {
    name: string
    type: string
    balance?: number
    currency?: string
    icon?: string
    color?: string
  }) {
    try {
      await ensureDbInit()
      const accountId = generateId()
      const now = new Date().toISOString()
      
      const accountData = {
        id: accountId,
        user_id: 'current_user',
        name: data.name,
        type: data.type,
        balance: data.balance || 0,
        currency: data.currency || 'CNY',
        icon: data.icon || '',
        color: data.color || '#3498db',
        is_active: 1,
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('finance_accounts', accountData)
      
      return createResponse({
        id: accountId,
        ...data,
        balance: data.balance || 0,
        isActive: true,
        createdAt: now,
        updatedAt: now
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '创建账户失败')
    }
  }
  
  /**
   * 更新账户
   */
  async updateAccount(id: string, data: any) {
    return await this.update(id, data)
  }
  
  /**
   * 删除账户
   */
  async deleteAccount(id: string) {
    return await this.hardDelete(id)
  }
  
  /**
   * 获取交易记录
   */
  async getTransactions(params: {
    page?: number
    pageSize?: number
    accountId?: string
    type?: string
    categoryId?: string
    startDate?: string
    endDate?: string
  } = {}) {
    try {
      await ensureDbInit()
      const {
        page = 1,
        pageSize = 20,
        accountId,
        type,
        categoryId,
        startDate,
        endDate
      } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams: any[] = []
      
      if (accountId) {
        whereClause += ' AND account_id = ?'
        whereParams.push(accountId)
      }
      
      if (type) {
        whereClause += ' AND type = ?'
        whereParams.push(type)
      }
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      const result = await database.paginate('finance_transactions', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'date DESC, created_at DESC'
      })
      
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取交易记录失败')
      }
      
      const paginationData = result.data || {}
        return createResponse({
          list: paginationData.list,
          page: paginationData.page,
          pageSize: paginationData.pageSize,
          total: paginationData.total
        })
      
    } catch (error: any) {
      return createErrorResponse(error.message || '获取交易记录失败')
    }
  }
  
  /**
   * 创建交易记录
   */
  async createTransaction(data: {
    accountId: string
    type: string
    amount: number
    categoryId?: string
    description?: string
    date?: string
    tags?: string[]
    location?: string
  }) {
    try {
      await ensureDbInit()
      const transactionId = generateId()
      const now = new Date().toISOString()
      
      const transactionData = {
        id: transactionId,
        user_id: 'current_user',
        account_id: data.accountId,
        type: data.type,
        amount: data.amount,
        category_id: data.categoryId || '',
        description: data.description || '',
        date: data.date || now.split('T')[0],
        tags: JSON.stringify(data.tags || []),
        location: data.location || '',
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('finance_transactions', transactionData)
      
      // 更新账户余额
      if (data.type === 'income') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance + ?, updated_at = ? WHERE id = ?',
          [data.amount, now, data.accountId]
        )
      } else if (data.type === 'expense') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance - ?, updated_at = ? WHERE id = ?',
          [data.amount, now, data.accountId]
        )
      }
      
      return createResponse({
        id: transactionId,
        ...data,
        createdAt: now,
        updatedAt: now
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '创建交易记录失败')
    }
  }
  
  /**
   * 更新交易记录
   */
  async updateTransaction(id: string, data: any) {
    try {
      await ensureDbInit()
      
      // 先获取原交易记录
      const originalResult = await database.query(
        'SELECT * FROM finance_transactions WHERE id = ? AND is_deleted = 0',
        [id]
      )
      
      if (originalResult.code !== 0 || !originalResult.data || originalResult.data.length === 0) {
        return createErrorResponse('交易记录不存在', 1003)
      }
      
      const original = originalResult.data[0]
      
      // 更新交易记录
      const updateData = {
        ...data,
        updated_at: new Date().toISOString()
      }
      
      if (updateData.tags) {
        updateData.tags = JSON.stringify(updateData.tags)
      }
      
      const rowsAffected = await database.update(
        'finance_transactions',
        updateData,
        'id = ? AND is_deleted = 0',
        [id]
      )
      
      if (rowsAffected === 0) {
        return createErrorResponse('交易记录不存在', 1003)
      }
      
      // 如果金额或类型发生变化，需要更新账户余额
      if (data.amount !== undefined || data.type !== undefined) {
        const newAmount = data.amount || original.amount
        const newType = data.type || original.type
        
        // 先撤销原交易对余额的影响
        if (original.type === 'income') {
          await database.executeSql(
            'UPDATE finance_accounts SET balance = balance - ? WHERE id = ?',
            [original.amount, original.account_id]
          )
        } else if (original.type === 'expense') {
          await database.executeSql(
            'UPDATE finance_accounts SET balance = balance + ? WHERE id = ?',
            [original.amount, original.account_id]
          )
        }
        
        // 应用新交易对余额的影响
        if (newType === 'income') {
          await database.executeSql(
            'UPDATE finance_accounts SET balance = balance + ? WHERE id = ?',
            [newAmount, original.account_id]
          )
        } else if (newType === 'expense') {
          await database.executeSql(
            'UPDATE finance_accounts SET balance = balance - ? WHERE id = ?',
            [newAmount, original.account_id]
          )
        }
      }
      
      return createResponse({ success: true })
    } catch (error: any) {
      return createErrorResponse(error.message || '更新交易记录失败')
    }
  }
  
  /**
   * 删除交易记录
   */
  async deleteTransaction(id: string) {
    try {
      await ensureDbInit()
      
      // 先获取交易记录
      const transactionResult = await database.query(
        'SELECT * FROM finance_transactions WHERE id = ? AND is_deleted = 0',
        [id]
      )
      
      if (transactionResult.code !== 0 || !transactionResult.data || transactionResult.data.length === 0) {
        return createErrorResponse('交易记录不存在', 1003)
      }
      
      const transaction = transactionResult.data[0]
      
      // 删除交易记录
      const rowsAffected = await database.delete('finance_transactions', 'id = ?', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('交易记录不存在', 1003)
      }
      
      // 撤销对账户余额的影响
      if (transaction.type === 'income') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance - ? WHERE id = ?',
          [transaction.amount, transaction.account_id]
        )
      } else if (transaction.type === 'expense') {
        await database.executeSql(
          'UPDATE finance_accounts SET balance = balance + ? WHERE id = ?',
          [transaction.amount, transaction.account_id]
        )
      }
      
      return createResponse({ success: true })
    } catch (error: any) {
      return createErrorResponse(error.message || '删除交易记录失败')
    }
  }
  
  /**
   * 获取财务统计
   */
  async getFinanceStats(params: {
    startDate?: string
    endDate?: string
    accountId?: string
  } = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate, accountId } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams: any[] = []
      
      if (accountId) {
        whereClause += ' AND account_id = ?'
        whereParams.push(accountId)
      }
      
      if (startDate) {
        whereClause += ' AND date >= ?'
        whereParams.push(startDate)
      }
      
      if (endDate) {
        whereClause += ' AND date <= ?'
        whereParams.push(endDate)
      }
      
      const incomeResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions WHERE ${whereClause} AND type = 'income'`,
        whereParams
      )
      
      if (incomeResult.code !== 0) {
        return createErrorResponse(incomeResult.message || '查询收入统计失败')
      }
      
      const expenseResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions WHERE ${whereClause} AND type = 'expense'`,
        whereParams
      )
      
      if (expenseResult.code !== 0) {
        return createErrorResponse(expenseResult.message || '查询支出统计失败')
      }
      
      const incomeData = incomeResult.data || []
      const expenseData = expenseResult.data || []
      const totalIncome = (incomeData[0] && typeof incomeData[0].total !== 'undefined') ? incomeData[0].total : 0
      const totalExpense = (expenseData[0] && typeof expenseData[0].total !== 'undefined') ? expenseData[0].total : 0
      const netIncome = totalIncome - totalExpense
      
      return createResponse({
        totalIncome,
        totalExpense,
        netIncome,
        transactionCount: await this.getTransactionCount(whereClause, whereParams)
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取财务统计失败')
    }
  }
  
  /**
   * 获取交易数量
   */
  private async getTransactionCount(whereClause: string, whereParams: any[]) {
    try {
      const result = await database.query(
        `SELECT COUNT(*) as count FROM finance_transactions WHERE ${whereClause}`,
        whereParams
      )
      
      if (result.code !== 0) {
        return 0
      }
      
      const data = result.data || []
      return (data[0] && typeof data[0].count !== 'undefined') ? data[0].count : 0
    } catch (error) {
      return 0
    }
  }
  
  /**
   * 获取预算列表
   */
  async getBudgets(params: {
    page?: number
    pageSize?: number
    categoryId?: string
    isActive?: boolean
  } = {}) {
    try {
      const { page = 1, pageSize = 20, categoryId, isActive } = params
      
      let whereClause = 'is_deleted = 0'
      let whereParams: any[] = []
      
      if (categoryId) {
        whereClause += ' AND category_id = ?'
        whereParams.push(categoryId)
      }
      
      if (isActive !== undefined) {
        whereClause += ' AND is_active = ?'
        whereParams.push(isActive ? 1 : 0)
      }
      
      const result = await database.paginate('finance_budgets', {
        page,
        pageSize,
        where: whereClause,
        whereParams,
        orderBy: 'created_at DESC'
      })
      
      if (result.code !== 0) {
        return createErrorResponse(result.message || '获取预算列表失败')
      }
      
      const paginationData = result.data || {}
      return createResponse({
        list: paginationData.list,
        page: paginationData.page,
        pageSize: paginationData.pageSize,
        total: paginationData.total
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取预算列表失败')
    }
  }
  
  /**
   * 创建预算
   */
  async createBudget(data: {
    name: string
    categoryId?: string
    amount: number
    period: string
    startDate: string
    endDate: string
  }) {
    try {
      await ensureDbInit()
      const budgetId = generateId()
      const now = new Date().toISOString()
      
      const budgetData = {
        id: budgetId,
        user_id: 'current_user',
        name: data.name,
        category_id: data.categoryId || '',
        amount: data.amount,
        period: data.period,
        start_date: data.startDate,
        end_date: data.endDate,
        spent: 0,
        is_active: 1,
        created_at: now,
        updated_at: now,
        is_deleted: 0
      }
      
      await database.insert('finance_budgets', budgetData)
      
      return createResponse({
        id: budgetId,
        ...data,
        spent: 0,
        isActive: true,
        createdAt: now,
        updatedAt: now
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '创建预算失败')
    }
  }
  
  /**
   * 更新预算
   */
  async updateBudget(id: string, data: any) {
    try {
      await ensureDbInit()
      const updateData = {
        ...data,
        updated_at: new Date().toISOString()
      }
      
      const rowsAffected = await database.update(
        'finance_budgets',
        updateData,
        'id = ? AND is_deleted = 0',
        [id]
      )
      
      if (rowsAffected === 0) {
        return createErrorResponse('预算不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error: any) {
      return createErrorResponse(error.message || '更新预算失败')
    }
  }
  
  /**
   * 删除预算
   */
  async deleteBudget(id: string) {
    try {
      await ensureDbInit()
      const rowsAffected = await database.delete('finance_budgets', 'id = ?', [id])
      
      if (rowsAffected === 0) {
        return createErrorResponse('预算不存在', 1003)
      }
      
      return createResponse({ success: true })
    } catch (error: any) {
      return createErrorResponse(error.message || '删除预算失败')
    }
  }
}

// 导出单例实例
export const financeController = new FinanceController()